package cn.edu.zjgsu.ito.service;



import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;



import cn.edu.zjgsu.ito.model.TeacherInfo;

import com.jfinal.kit.EncryptionKit;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;

import jxl.Cell;
import jxl.CellType;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

public class AdminTeaManageService {

	/**
	 * 根据上传的EXCEL表格，获取所有教师信息并存入数据库
	 * 黄凯航
	 * @throws IOException 
	 * @throws BiffException 
	 **/
	public String readTeacherTemplate(String fileName,String newFileName,String filePath) throws BiffException, IOException
	{
		Date date=new Date();
		DateFormat format=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		int i=0; //列
		int j=1; //行
		int k=-1;
		int rows=0;
		int breakRows=1;
		int breakExcel=1;
		int breakK=0;
		int countSuccess=0;
		int countUpdate=0;
		int countFail=0;

		String error="";
		String breakTNo="";
		String collegeName="";
		String type="Teacher Template（教师模板）";
		String uploader="Admin（教学秘书）";
		String identity="Teacher";
		String uploadTime=format.format(date);

		Record uploadFileRecord=new Record().set("type", type)
				.set("fileName", fileName)
				.set("newFileName", newFileName)
				.set("filePath", filePath)
				.set("uploader", uploader)
				.set("uploadTime", uploadTime);
		Db.save("upload_file", uploadFileRecord);

		Workbook rwb=null;
		Cell[] cells=new Cell[4];
		InputStream stream=new FileInputStream(filePath+"\\"+newFileName);
		rwb=Workbook.getWorkbook(stream);
		Sheet sheet=rwb.getSheet(0);
		cells[0]=sheet.getCell(0,j);
		rows=sheet.getRows();

		String[] tNos=new String[rows-1];

		while(true)
		{
			breakRows++;

			if(cells[0].getContents().trim().equals("")||cells[0].getType()==CellType.EMPTY)
			{
				if(breakRows==rows)
					break;
				j++;
				cells[0]=sheet.getCell(0, j);
				continue;
			}
			k++;
			tNos[k]=cells[0].getContents().trim();
			breakK=k;
			if(breakRows==rows)
				break;
			j++;
			cells[0]=sheet.getCell(0, j);
		}

		for(j=0;j<=breakK;j++)
		{
			if(breakExcel==0)
				break;
			for(k=j+1;k<=breakK;k++)
			{
				if(tNos[j].equals(tNos[k]))
				{
					breakExcel=0;
					breakTNo=tNos[j];
					break;
				}
			}
		}

		if(breakExcel==1)
		{
			breakRows=1;
			j=1;
			cells[0]=sheet.getCell(0, j);
			cells[1]=sheet.getCell(1, j);
			cells[2]=sheet.getCell(2, j);
			cells[3]=sheet.getCell(3, j);

			while(true)
			{
				breakRows++;
				if(cells[0].getContents().trim().equals("")||cells[0].getType()==CellType.EMPTY||
						cells[1].getContents().trim().equals("")||cells[1].getType()==CellType.EMPTY||
						cells[2].getContents().trim().equals("")||cells[2].getType()==CellType.EMPTY||
						cells[3].getContents().trim().equals("")||cells[3].getType()==CellType.EMPTY)
				{
					countFail++;
					j++;
					if(breakRows==rows)
						break;
					else
					{
						cells[0]=sheet.getCell(0, j);
						cells[1]=sheet.getCell(1, j);
						cells[2]=sheet.getCell(2, j);
						cells[3]=sheet.getCell(3, j);
					}
					continue;
				}

				List<Record> collegeInfo=Db.find("select * from college where collegeCode=?",cells[2].getContents().trim());
				if(collegeInfo.size()==0)
				{
					countFail++;
					j++;
					if(breakRows==rows)
						break;
					else
					{
						cells[0]=sheet.getCell(0, j);
						cells[1]=sheet.getCell(1, j);
						cells[2]=sheet.getCell(2, j);
						cells[3]=sheet.getCell(3, j);
					}
					continue;
				}
				collegeName=collegeInfo.get(0).get("collegeName");

				List<Record> teacherInfo=Db.find("select * from teacher_info");

				for(i=0;i<teacherInfo.size();i++)
				{
					Record singleTeacherInfo=teacherInfo.get(i);
					String tNo=singleTeacherInfo.get("tNo");
					if(tNo.equals(cells[0].getContents().trim()))				
						break;				
				}

				if(i==teacherInfo.size())
				{
					Record teacherRecord=new Record()
					.set("tNo", cells[0].getContents().trim())
					.set("tPwd", EncryptionKit.md5Encrypt(cells[0].getContents().trim()))
					.set("identity", identity)
					.set("createTime", format.format(date));

					Db.save("teacher", teacherRecord);

					Record teacherInfoRecord=new Record()
					.set("tNo", cells[0].getContents().trim())
					.set("tName", cells[1].getContents().trim())
					.set("collegeCode", cells[2].getContents().trim())
					.set("collegeName", collegeName)
					.set("title", cells[3].getContents().trim());

					Db.save("teacher_info", teacherInfoRecord);		

					countSuccess++;
				}
				else
				{
					Db.update("update teacher_info set tName=?,collegeCode=?,collegeName=?,title=? where tNo=?",
							cells[1].getContents().trim(),
							cells[2].getContents().trim(),
							collegeName,
							cells[3].getContents().trim(),
							cells[0].getContents().trim());
					boolean WARMING=updateAllTablesByNo(cells[0].getContents().trim(),cells[1].getContents().trim());
					if(WARMING==false)
						System.out.println("出事了！！！");
					countUpdate++;
				}
				j++;
				if(breakRows==rows)
					break;
				else
				{
					cells[0]=sheet.getCell(0, j);
					cells[1]=sheet.getCell(1, j);
					cells[2]=sheet.getCell(2, j);
					cells[3]=sheet.getCell(3, j);
				}
			}
			error="成功导入"+countSuccess+"条记录;成功覆盖"+countUpdate+"条记录；导入失败"+countFail+"条记录";
			return error;
		}
		else
		{
			error="所上传的Excel表中存在数据冲突——工号"+breakTNo;
			return error;
		}				
	}
	
	/**
	 * 增加单个教师方法
	 * 黄凯航
	 * @param teacherInfo
	 * @return
	 */
	public String addSingleTeacher(TeacherInfo teacherInfo)
	{
		String tNo=teacherInfo.getStr("tNo").trim();
		String tName=teacherInfo.getStr("tName").trim();
		String collegeCode=teacherInfo.getStr("collegeCode").trim();
		String title=teacherInfo.getStr("title").trim();
		String error="";
		String collegeName="";

		if(tNo.equals("")||tNo==null||
				tName.equals("")||tName==null||
				collegeCode.equals("")||collegeCode==null||
				title.equals("")||title==null)
		{
			error="请填写所有必填信息！";
			return error;
		}

		List<Record> collegeInfo=Db.find("select * from college where collegeCode=?",collegeCode);
		collegeName=collegeInfo.get(0).get("collegeName");
		teacherInfo.set("collegeName", collegeName);

		List<Record> teacherInfos=Db.find("select * from teacher_info where tNo=?",tNo);
		if(teacherInfos.size()==0)
		{
			teacherInfo.save();
			Date date=new Date();
			DateFormat format=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			String identity="Teacher";

			Record teacherRecord=new Record()
			.set("tNo", tNo)
			.set("tPwd", EncryptionKit.md5Encrypt(tNo))
			.set("identity", identity)
			.set("createTime", format.format(date));
			Db.save("teacher", teacherRecord);
			error="新增成功";
			return error;
		}
		else
		{
//			Db.update("update teacher_info set tName=?,collegeCode=?,collegeName=?,title=? where tNo=?",
//					tName,
//					collegeCode,
//					collegeName,
//					title,
//					tNo
//					);
//			boolean WARMING=updateAllTablesByNo(tNo,tName);
//			if(WARMING==false)
				System.out.println("出事了！！！");
			error="已经存在该工号";
			return error;
		}
	}

	/**
	 * 更新单个教师方法
	 * 黄凯航
	 * @param teacherInfo
	 * @return
	 */
	public String updateSingleTeacher(TeacherInfo teacherInfo)
	{
		String tName=teacherInfo.getStr("tName");
		String collegeCode=teacherInfo.getStr("collegeCode");
		System.out.println("!!!!"+collegeCode);
		String title=teacherInfo.getStr("title");
		String tNo=teacherInfo.getStr("tNo");
		String error="";
		String collegeName="";
		if(tName.equals("")||tName==null||
				collegeCode.equals("")||collegeCode==null||
				title.equals("")||title==null||
				tNo.equals("")||tNo==null)
		{
			error="请填写所有必填信息！";
			return error;
		}
		
		List<Record> collegeInfo=Db.find("select * from college where collegeCode=?",collegeCode);
		collegeName=collegeInfo.get(0).get("collegeName");
		teacherInfo.set("collegeName", collegeName);
		
		teacherInfo.update();
		boolean WARMING=updateAllTablesByNo(tNo,tName);
		if(WARMING==false)
			System.out.println("出事了！！！");
		error="修改成功";
		return error;
	}
	/**
	 * 删除单个教师方法
	 * @param tNo
	 */
	public void deleteOrNot(String tNo)
	{
		int roster=1;
		int studentGrade=1;
		int teacherTimetable=1;
		int studentTimetable=1;
		int majorTimetable=1;
		List<Record> rNullOrNot=Db.find("select * from roster where tNo=?",tNo);
		List<Record> sgNullOrNot=Db.find("select * from student_grade where tNo=?",tNo);
		List<Record> ttNullOrNot=Db.find("select * from teacher_timetable where tNo=?",tNo);
		List<Record> stNullOrNot=Db.find("select * from student_timetable where tNo=?",tNo);
		List<Record> mtNullOrNot=Db.find("select * from major_timetable where tNo=?",tNo);

		if(rNullOrNot.size()==0)
			roster=0;
		if(sgNullOrNot.size()==0)
			studentGrade=0;
		if(ttNullOrNot.size()==0)
			teacherTimetable=0;
		if(stNullOrNot.size()==0)
			studentTimetable=0;
		if(mtNullOrNot.size()==0)
			majorTimetable=0;

		if(roster==0&&studentGrade==0&&teacherTimetable==0&&studentTimetable==0&&majorTimetable==0)
		{
			Db.update("delete from teacher where tNo=?",tNo);
			Db.update("delete from teacher_info where tNo=?",tNo);
		}
	}
	/**
	 * 重置教师密码方法
	 * @param tNo
	 */
	public void rePassword(String tNo)
	{
		Db.update("update teacher set tPwd=? where tNo=?",EncryptionKit.md5Encrypt(tNo),tNo);
	}

	/**
	 * 更新所有表格
	 * 黄凯航
	 */
	public boolean updateAllTablesByNo(String tNo,String tName)
	{
		int caseOne=Db.update("update major_timetable set tName=? where tNo=?",tName,tNo);
//		int caseTwo=Db.update("update student_grade set tNo=? where tNo=?",tNo);
		int caseThree=Db.update("update student_timetable set tName=? where tNo=?",tName,tNo);

		if(caseOne>0&&caseThree>0)
			return true;
		else
			return false;
	}

}
